﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using Missing = System.Reflection.Missing;


public partial class WebUserControl_ControlBaoCaoDoanhSoNgayChuaKhoaSo : System.Web.UI.UserControl
{
    string str = BCDataCenter.Configuration.CurrentConnectionString;
    static string error = "";
   
    ClassExecute.ClassExecute dt;
    protected void Page_Load(object sender, EventArgs e)
    {
        
            if (!IsPostBack)
            {
                txtToDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
                BindDrop();
                if (Session["usertype"].ToString() != "ad")
                {
                    DropTTPP.SelectedValue = Session["sitecode"].ToString();
                    DropTTPP.Enabled = false;
                }
            }
            else
            {
                //try
                //{
                //    if (ViewState["db"] != null)
                //    {
                //        DataTable db = (DataTable)ViewState["db"];
                //        Title();
                //        ASPxPivotGrid1.DataSource = db;
                //        ASPxPivotGrid1.DataBind();
                //    }
                //}
                //catch 
                //{
                //    return;
                //}
            }
        
    }
    void BindPivotGrid()
    {

            dt = new ClassExecute.ClassExecute();
            bool kq = dt.ExecuteSQl_bool(str, CommandType.StoredProcedure, "[ls_getDailyOfftake]",ref error,
                        new SqlParameter("@siteCode",DropTTPP.SelectedValue.ToString()),
                        new SqlParameter("@userName",Session["username"].ToString()),
                        new SqlParameter("@frDate",txtFromDate.Text),
                        new SqlParameter("@toDate",txtToDate.Text));

            if (kq)
            {
                //ViewState["db"] = null;
               DataTable  db = dt.Get_Data_DataTable(str, ref error, "select * from dml_" + Session["username"] + "_getDailyOfftake", CommandType.Text);
                if (db.Rows.Count > 0)
                {
                    //ViewState["db"] = db;
                    Title();
                    ASPxPivotGrid1.DataSource = db;
                    ASPxPivotGrid1.DataBind();
                }
            }
           
       
    }
    public void Title()
    {
        ASPxPivotGrid1.Fields["Baocao"].Caption = "Báo cáo doanh số ngày của TTPP : " + DropTTPP.SelectedItem.ToString();
        ASPxPivotGrid1.Fields["FromDate"].Caption = "Từ ngày : " + txtFromDate.Text;
        ASPxPivotGrid1.Fields["ToDate"].Caption = "Đến ngày : " + txtToDate.Text;
        ASPxPivotGrid1.Fields["Time"].Caption = "Giờ lấy báo cáo : " + DateTime.Now.ToShortTimeString();
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
           
            BindPivotGrid();
        }
        catch (Exception ex)
        {
            BCDataCenter.ClassWriteLogErr obj = new BCDataCenter.ClassWriteLogErr();
            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            obj.ErrorLog(ex.Message, method);
        }
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        try
        {
            PivotExcel();
            
        }
        catch (Exception ex)
        {
            BCDataCenter.ClassWriteLogErr obj = new BCDataCenter.ClassWriteLogErr();
            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            obj.ErrorLog(ex.Message, method);
        }
    }
    protected void btPrint_Click(object sender, EventArgs e)
    {
        try
        {
            using (MemoryStream filestream = new MemoryStream())
            {

                string contentType = "application/ms-excel";
                string fileName = System.DateTime.Now.ToString("dd/MM/yyyy") + "_BaoCaoDoanhNgayChuaKhoaSo.xls";
                ASPxPivotGridExporter1.ExportToXls(filestream);
                byte[] buffer = filestream.GetBuffer();
                Response.Clear();
                Response.Buffer = false;
                Response.AppendHeader("Content-Type", contentType);
                Response.AppendHeader("Content-Transfer-Encoding", "binary");
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
                Response.BinaryWrite(buffer);
                Response.End();
            }
        }
        catch (Exception ex)
        {
            BCDataCenter.ClassWriteLogErr obj = new BCDataCenter.ClassWriteLogErr();
            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            obj.ErrorLog(ex.Message, method);
        }
    }
    void BindDrop()
    {
        dt = new ClassExecute.ClassExecute();
        DataTable ttpp=dt.Get_Data_DataTable(str,ref error,"[GET_SITENAME_ALL]",CommandType.StoredProcedure);
        DropTTPP.DataSource=ttpp;
        DropTTPP.DataTextField ="sitename";
        DropTTPP.DataValueField="Sitecode";
        DropTTPP.DataBind();
    }
    void PivotExcel()
    {
       
        if (File.Exists(Server.MapPath(Session["username"].ToString() + "_BCDoanhSoNgay.xls")))
        {
            File.Delete(Server.MapPath(Session["username"].ToString() + "_BCDoanhSoNgay.xls"));
        }
       

       // string connection = @"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;" + str;
        string connection =  @"OLEDB;Provider=SQLOLEDB.1;" + str;
       
        string cmd = "select * from dml_" + Session["username"].ToString() + "_getDailyOfftake"; 
        Excel.Application excel = new Excel.Application();
        try
        {  
            excel.Visible = false;
            excel.AlertBeforeOverwriting = false;
            Excel.Workbook workbook = excel.Workbooks.Add(Missing.Value);
            Excel.Worksheet sheet = (Excel.Worksheet)workbook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            Excel.Style style = workbook.Styles.Add("Style1", Missing.Value);
            style.Font.Bold = true;
            style.Font.Size = 32;
            sheet.Activate();
            Excel.Range range = ((Excel.Range)sheet.Cells[1, 1]);
            range.Style = style;
            range.Value2 = "BÁO CÁO DOANH SỐ";
            Excel.Range range1 = ((Excel.Range)sheet.Cells[2, 1]);
            range1.Value2 = "Ngày giờ lấy báo cáo :" + DateTime.Now.ToString();
            Excel.Range range2 = ((Excel.Range)sheet.Cells[3, 1]);
            range2.Value2 = "Từ ngày :" + txtFromDate.Text + " đến ngày :" + txtToDate.Text;

            Excel.PivotCache pivotCache = excel.ActiveWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, Missing.Value);

            //GÁN CHUỔI KẾT NỐI
            pivotCache.Connection  = connection;
            pivotCache.MaintainConnection = true;

            //GÁN CÂU LỆNH QUERY
            
            pivotCache.CommandText = cmd;
            pivotCache.CommandType = Excel.XlCmdType.xlCmdSql;
            Excel.PivotTables pivotTables = (Excel.PivotTables)sheet.PivotTables(Missing.Value);
            Excel.PivotTable pivotTable = pivotTables.Add(pivotCache, excel.ActiveCell[20, 1], "PivotTable11", Missing.Value, Missing.Value);

            object[] noSub = { false, false, false, false, false, false, false, false, false, false, false, false };

            //ĐẶT TẠI VÙNG PAGE
            Excel.PivotField pageField1 = (Excel.PivotField)pivotTable.PivotFields("mTown");
            pageField1.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField1.Caption = "Tỉnh/Thành Phố";

            Excel.PivotField pageField2 = (Excel.PivotField)pivotTable.PivotFields("mprovince");
            pageField2.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField2.Caption = "Quận/Huyện";

            Excel.PivotField pageField3 = (Excel.PivotField)pivotTable.PivotFields("salespname");
            pageField3.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField3.Caption = "Tên TVV";

            Excel.PivotField pageField4 = (Excel.PivotField)pivotTable.PivotFields("TeamLeader");
            pageField4.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField4.Caption = "Nhóm trưởng";


            Excel.PivotField pageField5 = (Excel.PivotField)pivotTable.PivotFields("ProgramID");
            pageField5.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField5.Caption = "Loại dòng";

            Excel.PivotField pageField6 = (Excel.PivotField)pivotTable.PivotFields("OffType");
            pageField6.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField6.Caption = "Loại doanh số";

            Excel.PivotField pageField7 = (Excel.PivotField)pivotTable.PivotFields("salespkey");
            pageField7.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField7.Caption = "Mã TVV";

            Excel.PivotField pageField8 = (Excel.PivotField)pivotTable.PivotFields("SkuCode");
            pageField8.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField8.Caption = "Mã hàng";

            Excel.PivotField pageField9 = (Excel.PivotField)pivotTable.PivotFields("SiteCode");
            pageField9.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField9.Caption = "Đăng ký tại";

            Excel.PivotField pageField10 = (Excel.PivotField)pivotTable.PivotFields("InclassName");
            pageField10.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField10.Caption = "Nhãn hiệu";

            Excel.PivotField pageField11 = (Excel.PivotField)pivotTable.PivotFields("CommodName");
            pageField11.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField11.Caption = "Ngành hàng";

            Excel.PivotField pageField12 = (Excel.PivotField)pivotTable.PivotFields("ProductName");
            pageField12.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField12.Caption = "Sản phẩm";

            Excel.PivotField pageField13 = (Excel.PivotField)pivotTable.PivotFields("ItemName");
            pageField13.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField13.Caption = "Tên hàng";

            Excel.PivotField pageField15 = (Excel.PivotField)pivotTable.PivotFields("TVV");
            pageField15.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField15.Caption = "TVV";

           

            //ĐẶT TẠI VÙNG ROW

            Excel.PivotField pageField14 = (Excel.PivotField)pivotTable.PivotFields("BelongTo");
            pageField14.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            pageField14.Caption = "Mua hàng tại";
           
            //Đat tai vung colum
            Excel.PivotField pageField16 = (Excel.PivotField)pivotTable.PivotFields("OffDate");
            pageField16.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
            pageField16.Caption = "Ngày";

            //Dat vung data
            Excel.PivotField pageField17 = (Excel.PivotField)pivotTable.PivotFields("OffQty");
            pageField17.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
            pageField17.Caption = "Số lượng";

            Excel.PivotField pageField18 = (Excel.PivotField)pivotTable.PivotFields("OffAmt");
            pageField18.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
            pageField18.Caption = "Doanh số";

           
            workbook.SaveAs(Server.MapPath(Session["username"].ToString() + "_BCDoanhSoNgay.xls"), Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            workbook.Close(true, Missing.Value, Missing.Value);
            excel.Quit();

            saveFile(this.Response, Server.MapPath(Session["username"].ToString() + "_BCDoanhSoNgay.xls"));
        }
        catch
        {
           
        }
        finally
        {
            // Make sure we release the reference to the underlying COM object
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
        }
    }
    void saveFile(System.Web.HttpResponse page, string PathfileName)
    {
        string filename = PathfileName;
        if (filename != "")
        {
            string path = filename;
            System.IO.FileInfo file = new System.IO.FileInfo(path);
            if (file.Exists)
            {
                page.Clear();
                page.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
                page.AddHeader("Content-Length", file.Length.ToString());
                page.ContentType = "application/octet-stream";
                page.WriteFile(file.FullName);
                page.End();

            }
        }

    }
   
}
